-- @owner: @zhanghuan96
-- @date: 2024/11/12
-- @testpoint: 权限测试，当前用户解析对象的使用权限，部分合理报错
@conn PrimaryDbAmode;
--前置条件
drop extension if exists gms_utility;
create extension gms_utility;
--step1:创建用户;expect:创建成功
drop user if exists new_user cascade;
CREATE USER new_user WITH PASSWORD 'Test@123';
ALTER SESSION SET SESSION AUTHORIZATION new_user PASSWORD 'Test@123';
--step2:使用新创建的用户创建表;expect:创建成功
drop table if exists example_table;
CREATE TABLE example_table (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INT
);
--step3:回收用户权限;expect:回收成功
REVOKE ALL PRIVILEGES ON TABLE example_table FROM new_user;
--step4:查询表;expect:无权限
select * from new_user.example_table;
--step5:使用函数;expect:报错
declare
    name varchar2 := 'new_user.example_table';
    context integer := 0;
    schema  varchar2;
    part1   varchar2;
    part2   varchar2;
    dblink  varchar2;
    part1_type  number;
    object_number   number;
begin
    gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
    raise INFO 'INFO:schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
end;
/
--step6:创建存储过程;expect:创建成功
drop function if exists calculate_square;
CREATE OR REPLACE FUNCTION calculate_square(input_number INTEGER)
RETURNS INTEGER AS $$
BEGIN
    RETURN input_number * input_number;
END;
$$ LANGUAGE plpgsql;
/
REVOKE all privileges ON procedure calculate_square(INTEGER) FROM new_user;
REVOKE all privileges ON procedure calculate_square(INTEGER) FROM public;
--step7:调用;expect:报错
SELECT new_user.calculate_square(5);
--step8:执行函数;expect:报错
declare
    name varchar2 := 'new_user.calculate_square';
    context integer := 1;
    schema  varchar2;
    part1   varchar2;
    part2   varchar2;
    dblink  varchar2;
    part1_type  number;
    object_number   number;
begin
    gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
    raise info 'INFO:schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
end;
/
--step9:清理环境;expect:清理成功
ALTER SESSION SET SESSION AUTHORIZATION default;
drop user new_user cascade;
drop table if exists example_table;
drop function if exists calculate_square;
drop extension if exists gms_utility;
